package storage.manager;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.mapdb.DB;
import org.mapdb.Serializer;

import logging.transaction.TransactionLogger;
import sql.constraints.ConstraintType;
import sql.constraints.ConstraintUtils;
import sql.constraints.ConstraintUtils.PrepareConstraintChangesResult;
import sql.constraints.TableConstraint;
import storage.StorageException;

public class TableManager {
    @SuppressWarnings("unchecked")
    public static int createTable(String dbName, String tableName, List<String[]> basicColumnDefinitions) {
        // 1. 从TransactionLogger获取DB实例
        DB db = TransactionLogger.getInstance().getCurrentTransactionDB();

        // 2. 检查事务状态和数据库上下文
        if (db == null) {
            System.err.println("错误: createTable 操作需要在活动的事务中执行。");
            return 0; 
        }
        String activeTransactionDbName = TransactionLogger.getInstance().getTransactionDatabaseName();
        if (activeTransactionDbName == null || !activeTransactionDbName.equals(dbName)) {
            System.err.println("错误: createTable 操作的数据库 '" + dbName + "' 与当前活动事务的数据库 '" + activeTransactionDbName + "' 不符。");
            return 0;
        }
        if (db.isClosed()) {
            System.err.println("错误: 与当前事务关联的数据库实例已关闭。事务将被回滚。");
            TransactionLogger.getInstance().rollbackTransaction(); 
            return 0;
        }

        try {
            // 3. 检查表是否已存在于TableInfo中 (使用事务db实例)
            Map<String, Object> tableInfo = (Map<String, Object>) db
                .hashMap("TableInfo")
                .keySerializer(Serializer.STRING)
                .valueSerializer(Serializer.JAVA)
                .createOrOpen()
                .getOrDefault("tables", new HashMap<String, Object>());

            if (tableInfo == null) { // 应该不会是null，因为getOrDefault
                tableInfo = new HashMap<>();
            }
            List<String> tablesList = (List<String>) tableInfo.getOrDefault("tablesList", new ArrayList<String>());

            if (tablesList.contains(tableName)) {
                System.err.println("❗ 表 [" + tableName + "] 已存在");
                return 0; // 或者抛出异常，让调用者决定如何处理
            }

            // 4. 添加表名到表列表
            tablesList.add(tableName);
            tableInfo.put("tablesList", tablesList);
            db.hashMap("TableInfo")
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("tables", tableInfo);

            // 5. 创建表信息结构 - 简化版
            // 5.1 存储列信息 - 所有列初始 nullable = true
            List<Map<String, Object>> columns = new ArrayList<>();
            for (String[] columnDef : basicColumnDefinitions) { // columnDef is now [name, type, length]
                Map<String, Object> columnInfo = new HashMap<>();
                String columnName = columnDef[0];
                columnInfo.put("name", columnName);      
                columnInfo.put("type", columnDef[1]);      
                columnInfo.put("length", Integer.parseInt(columnDef[2])); 
                columnInfo.put("nullable", true); // IMPORTANT: Default to true
                columns.add(columnInfo);
            }
            db.hashMap("columns_" + tableName)
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", columns);

            // 5.2 创建空的约束、键、索引元数据集合
            db.hashMap("constraints_" + tableName)
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", new ArrayList<Map<String, Object>>());
            
            // keys_ Map: 如果完全不使用，可以考虑不创建。如果某些旧逻辑或FK可能仍间接依赖，则保留为空。
            db.hashMap("keys_" + tableName) 
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", new ArrayList<Map<String, Object>>());

            db.hashMap("indexes_" + tableName)
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", new ArrayList<Map<String, Object>>());

            // 创建表数据存储
            db.hashMap("data_" + tableName)
              .keySerializer(Serializer.LONG).valueSerializer(Serializer.JAVA).createOrOpen();

            System.out.println("✅ 表 [" + tableName + "] 的基础结构已在事务中准备创建 (所有列默认为可空)");
            return 1; 

        } catch (Exception e) {
            System.err.println("❌ 事务中的表创建操作失败 (将由调用者回滚整个事务): " + tableName + " - " + e.getMessage());
            throw e; 
        }
    }

    /**
     * 删除表
     * 包括：从TableInfo中删除表名、删除表结构信息、清空表数据
     *
     * @param dbName 数据库名
     * @param tableName 表名
     * @return 操作结果：1成功，0失败
     */
    @SuppressWarnings("unchecked")
    public static int deleteTable(String dbName, String tableName) {
        // 1. 从TransactionLogger获取DB实例
        DB db = TransactionLogger.getInstance().getCurrentTransactionDB();

        // 2. 检查事务状态和数据库上下文
        if (db == null) {
            System.err.println("错误: deleteTable 操作需要在活动的事务中执行。");
            return 0; 
        }
        String activeTransactionDbName = TransactionLogger.getInstance().getTransactionDatabaseName();
        if (activeTransactionDbName == null || !activeTransactionDbName.equals(dbName)) {
            System.err.println("错误: deleteTable 操作的数据库 '" + dbName + "' 与当前活动事务的数据库 '" + activeTransactionDbName + "' 不符。");
            return 0;
        }
        if (db.isClosed()) {
            System.err.println("错误: 与当前事务关联的数据库实例已关闭。事务将被回滚。");
            TransactionLogger.getInstance().rollbackTransaction(); 
            return 0;
        }

        try {
            // 3. 检查表是否存在 (使用事务db实例, 或者调用事务化的tableExists方法)
            // 注意: 如果tableExists尚未事务化，直接在这里检查可能更简单
             Map<String, Object> preCheckTableInfo = (Map<String, Object>) db
                .hashMap("TableInfo")
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("tables", new HashMap<String, Object>());
             List<String> preCheckTablesList = (List<String>) preCheckTableInfo.getOrDefault("tablesList", new ArrayList<String>());
             if (!preCheckTablesList.contains(tableName)) {
                 System.err.println("❌ 表 [" + tableName + "] 不存在 (事务内检查)");
                 return 0;
             }
            
            // 4. 从TableInfo中删除表名 (使用事务db实例)
            Map<String, Object> tableInfo = (Map<String, Object>) db
                .hashMap("TableInfo")
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("tables", new HashMap<String, Object>());
            
            // tableInfo本身不应为null，但内部的list可能为空或不存在
            if (tableInfo.containsKey("tablesList")) {
                List<String> tablesList = (List<String>) tableInfo.get("tablesList");
                if (tablesList == null) tablesList = new ArrayList<>(); // 防御空指针

                boolean removed = tablesList.remove(tableName);
                if (removed) {
                    tableInfo.put("tablesList", tablesList); // 更新列表
                    db.hashMap("TableInfo")
                      .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                      .createOrOpen().put("tables", tableInfo); // 保存更新后的tableInfo
                    System.out.println("✅ 已在事务中准备从TableInfo删除表 [" + tableName + "]");
                } else {
                    // 这理论上不应发生，因为我们前面检查了表存在
                    System.err.println("错误: 表 [" + tableName + "] 在TableInfo列表中未找到，尽管之前检查存在。可能存在并发问题或逻辑错误。");
                    return 0;
                }
            } else {
                System.err.println("错误: TableInfo结构不完整，缺少tablesList。无法删除表。");
                return 0;
            }

            // 5. 清除表相关的MapDB集合 (使用事务db实例)
            String[] mapPrefixesToDelete = {"columns_", "keys_", "constraints_", "indexes_", "data_"};
            for (String prefix : mapPrefixesToDelete) {
                String mapName = prefix + tableName;
                // MapDB没有直接删除map的方法，但可以清空它或让它在下次打开时被忽略
                // 简单起见，我们清空它。注意：这不会回收空间，直到数据库压缩。
                try {
                     // 假设所有这些map都存在，如果不存在MapDB会创建它们再清空
                     // 更好的方式是检查是否存在，但更复杂
                     if (prefix.equals("data_")) { // data map uses Long key
                         db.hashMap(mapName).keySerializer(Serializer.LONG).valueSerializer(Serializer.JAVA).createOrOpen().clear();
                     } else { // metadata maps use String key
                         db.hashMap(mapName).keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().clear();
                     }
                     System.out.println("✅ 已在事务中准备清空Map: " + mapName);
                } catch (Exception mapClearEx) {
                    System.err.println("警告: 清空Map '" + mapName + "' 时出错: " + mapClearEx.getMessage() + " (继续删除操作)");
                    // 可以选择继续或中断事务
                }
            }

            // 移除了 db.commit();
            System.out.println("✅ 表 [" + tableName + "] 已在事务中准备删除");

            // systemDBEngine.removeTableFromDatabase(dbName, tableName); // 这个调用也需要事务化或在提交后执行

            return 1; // 操作已在事务中排队

        } catch (Exception e) {
            System.err.println("❌ 事务中的表删除操作失败 (将由调用者回滚整个事务): " + tableName + " - " + e.getMessage());
            // e.printStackTrace();
            throw e; // 直接重新抛出原始异常
        }
    }

    /**
     * 获取数据库的表列表
     *
     * @param ownerName 数据库所有者名称
     * @param dbName 数据库名称
     * @return 表名列表
     */
    @SuppressWarnings("unchecked")
    public static List<String> getTables(String ownerName, String dbName) {
        TransactionLogger txLogger = TransactionLogger.getInstance();
        DB db = null;
        boolean dbAcquiredInternally = false;

        // 优先使用当前事务的DB实例 (如果匹配)
        if (txLogger.isTransactionActive() &&
            dbName.equals(txLogger.getTransactionDatabaseName()) &&
            ownerName.equals(txLogger.getTransactionOwnerName())) {
            
            db = txLogger.getCurrentTransactionDB();
            if (db == null || db.isClosed()) {
                System.err.println("错误 (getTables): 活动事务的DB实例无效或已关闭 (数据库: " + ownerName + "." + dbName + ")");
                return new ArrayList<>();
            }
            System.out.println("[DEBUG] TableManager.getTables: Using active transaction DB for " + ownerName + "." + dbName);
        } else {
            // 没有匹配的活动事务，尝试独立获取DB实例
            System.out.println("[DEBUG] TableManager.getTables: No active transaction or mismatch. Attempting to get DB independently for " + ownerName + "." + dbName);
            try {
                db = DatabaseManager.getDatabase(ownerName, dbName); // 获取DB实例
                if (db == null || db.isClosed()) {
                     System.err.println("错误 (getTables): 无法独立获取或打开数据库实例: " + ownerName + "." + dbName);
                     return new ArrayList<>();
                }
                dbAcquiredInternally = true;
                System.out.println("[DEBUG] TableManager.getTables: Acquired DB instance independently for " + ownerName + "." + dbName);
            } catch (StorageException e) {
                System.err.println("错误 (getTables): 独立获取数据库实例时出错 (" + ownerName + "." + dbName + "): " + e.getMessage());
                return new ArrayList<>();
            }
        }

        try {
            Map<String, Object> tableInfoRoot = (Map<String, Object>) db
                .hashMap("TableInfo") // Root map name for table metadata
                .keySerializer(Serializer.STRING)
                .valueSerializer(Serializer.JAVA)
                .createOrOpen(); // open if exists, create if not

            // The actual list of table names is nested: "TableInfo" -> "tables" (key) -> Map -> "tablesList" (key) -> List<String>
            Map<String, Object> tablesMap = (Map<String, Object>) tableInfoRoot.get("tables");

            if (tablesMap == null || !tablesMap.containsKey("tablesList")) {
                 System.out.println("[DEBUG] TableManager.getTables: No 'tablesList' found in 'TableInfo' -> 'tables' for " + ownerName + "." + dbName + ". Returning empty list.");
                return new ArrayList<>(); // No tables list found
            }

            List<String> tablesList = (List<String>) tablesMap.get("tablesList");
            return tablesList != null ? new ArrayList<>(tablesList) : new ArrayList<>();
        } catch (Exception e) {
            System.err.println("错误 (getTables): 在数据库 '" + ownerName + "." + dbName + "' 中检索表列表时发生异常: " + e.getMessage());
            // e.printStackTrace(); // For detailed debugging
            return new ArrayList<>();
        } finally {
            if (dbAcquiredInternally && db != null) {
                // 只释放我们内部获取的DB实例
                DatabaseManager.releaseDatabase(ownerName, dbName);
                System.out.println("[DEBUG] TableManager.getTables: Released independently acquired DB instance for " + ownerName + "." + dbName);
            }
        }
    }

    /**
     * 获取表信息
     *
     * @param dbName 数据库名
     * @param tableName 表名
     * @return 表信息Map，包含列、键、约束和索引
     */
    public static Map<String, Object> getTableInfo(String dbName, String tableName) {
        // 1. 从TransactionLogger获取DB实例
        TransactionLogger txLogger = TransactionLogger.getInstance();
        DB db = null;

        // 2. 检查事务状态和数据库上下文
        if (!txLogger.isTransactionActive()) {
            System.err.println("错误 (getTableInfo): 操作需要在活动的事务中执行。当前没有活动事务。 (数据库: " + dbName + ", 表: " + tableName + ")");
            return new HashMap<>();
        }
        String activeTxDbName = txLogger.getTransactionDatabaseName();
        if (activeTxDbName == null || !activeTxDbName.equals(dbName)) {
            System.err.println("错误 (getTableInfo): 操作的数据库 '" + dbName + "' 与当前活动事务的数据库 '" + 
                               (activeTxDbName == null ? "未知" : activeTxDbName) + "' 不符。 (表: " + tableName + ")");
            return new HashMap<>();
        }
        db = txLogger.getCurrentTransactionDB();
        if (db == null || db.isClosed()) {
            System.err.println("错误 (getTableInfo): 操作无法执行。与当前活动事务关联的数据库实例 ('" + dbName + "') 无效或已关闭。 (表: " + tableName + ")");
            return new HashMap<>();
        }

        try {
            // 3. 检查表是否存在 (使用已确定的事务db实例)
            // 这个表存在性检查也应该使用事务性DB
            Map<String, Object> tableInfoMap = (Map<String, Object>) db
                .hashMap("TableInfo")
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("tables", new HashMap<String, Object>());
            @SuppressWarnings("unchecked")
            List<String> tablesList = (List<String>) tableInfoMap.getOrDefault("tablesList", new ArrayList<String>());
            if (!tablesList.contains(tableName)) {
                System.err.println("❌ 表 [" + tableName + "] 不存在 (在事务上下文中检查 - getTableInfo)");
                return new HashMap<>(); 
            }

            // 4. 创建结果Map并获取信息 (使用已确定的事务db实例)
            Map<String, Object> result = new HashMap<>();

            @SuppressWarnings("unchecked")
            List<Map<String, Object>> columns = (List<Map<String, Object>>) db
                .hashMap("columns_" + tableName)
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("metadata", new ArrayList<Map<String, Object>>());
            result.put("columns", columns);

            @SuppressWarnings("unchecked")
            List<Map<String, Object>> keys = (List<Map<String, Object>>) db
                .hashMap("keys_" + tableName)
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("metadata", new ArrayList<Map<String, Object>>());
            result.put("keys", keys);

            @SuppressWarnings("unchecked")
            List<Map<String, Object>> constraints = (List<Map<String, Object>>) db
                .hashMap("constraints_" + tableName)
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("metadata", new ArrayList<Map<String, Object>>());
            result.put("constraints", constraints);

            @SuppressWarnings("unchecked")
            List<Map<String, Object>> indexes = (List<Map<String, Object>>) db
                .hashMap("indexes_" + tableName)
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("metadata", new ArrayList<Map<String, Object>>());
            result.put("indexes", indexes);

            return result;
        } catch (Exception e) {
            System.err.println("❌ 获取表信息失败 (读取时，在事务上下文中): " + dbName + "/" + tableName + " - " + e.getMessage());
            return new HashMap<>(); // Return empty map on error
        } finally {
            // DB实例来自TransactionLogger，不由本方法关闭/释放。
        }
    }

    /**
     * 检查表是否存在
     *
     * @param dbName 数据库名
     * @param tableName 表名
     * @return 存在返回true，不存在返回false
     */
    public static boolean tableExists(String dbName, String tableName) {
        TransactionLogger txLogger = TransactionLogger.getInstance();
        String ownerName = null;

        if (txLogger.isTransactionActive()) {
            String txDbName = txLogger.getTransactionDatabaseName();
            String txOwnerName = txLogger.getTransactionOwnerName();
            if (dbName.equals(txDbName) && txOwnerName != null) {
                ownerName = txOwnerName;
            } else {
                // dbName does not match active transaction's db or owner is missing in tx
                System.err.println("[WARN] TableManager.tableExists: Provided dbName '" + dbName +
                                   "' does not match active transaction DB '" + txDbName + 
                                   "' or transaction owner is null. Cannot reliably check table existence outside of a fully matching transaction context without an explicit owner.");
                return false; // Cannot determine owner, or context mismatch
            }
        } else {
            // No active transaction
            System.err.println("[WARN] TableManager.tableExists: Called for dbName '" + dbName +
                               "' without an active transaction. Cannot determine owner to call getTables(owner,db). Consider providing owner or ensuring transaction context.");
            return false; // Cannot determine owner without a transaction or explicit parameter
        }

        // If we reached here, ownerName should be set from the transaction context
        List<String> tables = getTables(ownerName, dbName);
        return tables.contains(tableName);
    }

    /**
     * 设置表的主键
     *
     * @param dbName 数据库名
     * @param tableName 表名
     * @param primaryKeyColumns 主键列名列表
     * @return 操作结果：1成功，0失败
     */
    @SuppressWarnings("unchecked")
    public static int setPrimaryKey(String dbName, String tableName, List<String> primaryKeyColumns) {
        // 1. 从TransactionLogger获取DB实例
        DB db = TransactionLogger.getInstance().getCurrentTransactionDB();

        // 2. 检查事务状态和数据库上下文
        if (db == null) {
            System.err.println("错误: setPrimaryKey 操作需要在活动的事务中执行。");
            return 0; 
        }
        String activeTransactionDbName = TransactionLogger.getInstance().getTransactionDatabaseName();
        if (activeTransactionDbName == null || !activeTransactionDbName.equals(dbName)) {
            System.err.println("错误: setPrimaryKey 操作的数据库 '" + dbName + "' 与当前活动事务的数据库 '" + activeTransactionDbName + "' 不符。");
            return 0;
        }
        if (db.isClosed()) {
            System.err.println("错误: 与当前事务关联的数据库实例已关闭。事务将被回滚。");
            TransactionLogger.getInstance().rollbackTransaction(); 
            return 0;
        }

        try {
            // 3. 检查表是否存在 (使用事务db实例)
            // Note: tableExists 调用目前未事务化，可能需要调整或直接检查
            Map<String, Object> preCheckTableInfo = (Map<String, Object>) db
                .hashMap("TableInfo")
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("tables", new HashMap<String, Object>());
            List<String> preCheckTablesList = (List<String>) preCheckTableInfo.getOrDefault("tablesList", new ArrayList<String>());
            if (!preCheckTablesList.contains(tableName)) {
                System.err.println("❌ 表 [" + tableName + "] 不存在 (事务内检查)");
                return 0;
            }
            // 移除旧的独立 tableExists 检查
            // if (!tableExists(dbName, tableName)) { ... } 
            
            // 移除旧的独立 getDatabase 调用
            // db = systemDBEngine.getDatabase(dbName); 
            // if (db == null) { ... }

            // 4. 获取列信息以验证主键列是否存在 (使用事务db实例)
            List<Map<String, Object>> columnsMeta = (List<Map<String, Object>>) db
                .hashMap("columns_" + tableName)
                .keySerializer(Serializer.STRING)
                .valueSerializer(Serializer.JAVA)
                .createOrOpen()
                .getOrDefault("metadata", new ArrayList<Map<String, Object>>());

            List<String> columnNames = new ArrayList<>();
            for (Map<String, Object> column : columnsMeta) {
                 if (column != null && column.get("name") instanceof String) { 
                    columnNames.add((String) column.get("name"));
                 }
            }

            for (String pkColumn : primaryKeyColumns) {
                if (!columnNames.contains(pkColumn)) {
                    System.err.println("❌ 主键列 [" + pkColumn + "] 不存在于表 [" + tableName + "] 中");
                    return 0;
                }
            }

            // 5. 获取并更新约束信息 (使用事务db实例)
            List<Map<String, Object>> constraintsMapList = (List<Map<String, Object>>) db
                .hashMap("constraints_" + tableName)
                .keySerializer(Serializer.STRING)
                .valueSerializer(Serializer.JAVA)
                .createOrOpen()
                .getOrDefault("metadata", new ArrayList<Map<String, Object>>());

            // 创建一个新的列表来存储更新后的约束，避免在迭代时修改
            List<Map<String, Object>> updatedConstraintsMapList = new ArrayList<>();
            boolean primaryKeyExists = false;
            for (Map<String, Object> constraintMap : constraintsMapList) {
                if (constraintMap != null) {
                    String typeStr = (String) constraintMap.get("type");
                    if (ConstraintType.PRIMARY_KEY.name().equals(typeStr)) {
                        primaryKeyExists = true;
                        // 不添加到 updatedConstraintsMapList，因为我们将添加新的或替换它
                    } else {
                        updatedConstraintsMapList.add(constraintMap); // 保留其他约束
                    }
                }
            }
            
            if (primaryKeyExists) {
                 System.out.println("提示: 表 [" + tableName + "] 已存在主键，将进行替换。");
            }

            // 添加新的主键约束
            TableConstraint primaryKeyConstraint = new TableConstraint(
                "pk_" + tableName, 
                ConstraintType.PRIMARY_KEY, 
                new ArrayList<>(primaryKeyColumns) // Store a copy
            );
            updatedConstraintsMapList.add(primaryKeyConstraint.toMap());

            // 确保主键列也有 NOT NULL 约束
            for (String pkColumn : primaryKeyColumns) {
                boolean nnExistsForPkCol = false;
                for (Map<String, Object> constraintMap : updatedConstraintsMapList) {
                    String typeStr = (String) constraintMap.get("type");
                    if (ConstraintType.NOT_NULL.name().equals(typeStr)) {
                        List<String> constrainedCols = (List<String>) constraintMap.get("columns");
                        if (constrainedCols != null && constrainedCols.contains(pkColumn)) {
                            nnExistsForPkCol = true;
                            break;
                        }
                    }
                }
                if (!nnExistsForPkCol) {
                    TableConstraint nnConstraint = new TableConstraint(
                        "nn_" + tableName + "_" + pkColumn, // 确保约束名称唯一
                        ConstraintType.NOT_NULL,
                        Collections.singletonList(pkColumn)
                    );
                    updatedConstraintsMapList.add(nnConstraint.toMap());
                    System.out.println("提示: 为主键列 [" + pkColumn + "] 自动添加了 NOT NULL 约束。");

                    // 同时更新列元数据中的 "nullable" 属性
                    for(Map<String, Object> colMeta : columnsMeta) {
                        if (pkColumn.equals(colMeta.get("name"))) {
                            colMeta.put("nullable", false);
                            break;
                        }
                    }
                }
            }
            // 保存更新后的列元数据 (因为 nullable 可能已更改)
             db.hashMap("columns_" + tableName)
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", columnsMeta);


            db.hashMap("constraints_" + tableName)
              .keySerializer(Serializer.STRING)
              .valueSerializer(Serializer.JAVA)
              .createOrOpen()
              .put("metadata", updatedConstraintsMapList); 
            
            // 从 keys_metadata 移除旧的主键逻辑 (如果它曾存在那里)
            // 由于我们是从列定义解析约束，`keys_` 可能不包含主键，除非 ALTER TABLE ADD CONSTRAINT PRIMARY KEY 曾将其添加到那里。
            // 为了保持一致性，如果 `keys_` map 被保留，确保它不包含一个名为 "PRIMARY" 的键。
            // 鉴于之前的设计是将 PRIMARY KEY 存在 keys_ 中，我们需要从中移除。
            List<Map<String, Object>> keysMeta = (List<Map<String, Object>>) db
                .hashMap("keys_" + tableName)
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("metadata", new ArrayList<Map<String,Object>>());
            
            List<Map<String, Object>> updatedKeysMeta = new ArrayList<>();
            for (Map<String, Object> key : keysMeta) {
                if (key != null && !"PRIMARY".equals(key.get("type"))) {
                    updatedKeysMeta.add(key);
                }
            }
            db.hashMap("keys_" + tableName)
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", updatedKeysMeta);


            // 移除了 db.commit();
            System.out.println("✅ 已在事务中准备为表 [" + tableName + "] 设置/更新主键约束");

            return 1; // 操作已在事务中排队

        } catch (Exception e) {
            System.err.println("❌ 事务中的设置主键操作失败 (将由调用者回滚整个事务): " + tableName + " - " + e.getMessage());
            // e.printStackTrace();
            throw e; // 直接重新抛出原始异常
        }
    }

    /**
     * 给表结构增加列
     * columnName：要添加的列
     * columnType：该列数据类型
     */
    public static int addColumn(String dbName, String tableName, String columnName, String columnType){
        System.out.println("添加列："+columnName+"，数据类型："+columnType);
        // DB db = null; // 移除了未使用的DB声明
        try {
            // 解析数据类型，提取类型和长度
            String type = columnType;
            String lengthStr = "255"; // 默认长度
            
            if (columnType.contains("(") && columnType.contains(")")) {
                int openBracket = columnType.indexOf("(");
                int closeBracket = columnType.indexOf(")");
                
                if (openBracket > 0 && closeBracket > openBracket) {
                    type = columnType.substring(0, openBracket).trim();
                    lengthStr = columnType.substring(openBracket + 1, closeBracket).trim();
                }
            }
            
            String[] columnDef = new String[3];
            columnDef[0] = columnName;
            columnDef[1] = type;
            columnDef[2] = lengthStr;
            
            // DataManager.updateColumnStructure 会处理事务和DB实例
            // 如果它失败，会抛出异常，该异常应由此方法向上传播
            return storage.data.DataManager.updateColumnStructure(dbName, tableName, "ADD", columnDef);
        } catch (Exception e) {
            System.err.println("❌ (TableManager)添加列时发生错误: " + dbName + "." + tableName + "." + columnName + " - " + e.getMessage());
            // 重新抛出异常，以便上层（如Session）可以处理事务回滚
            throw e; 
        }
    }
    
    /**
     * 给表结构删除列
     * columnName：要删除的列
     * ps：注意检查有没有这个列
     */
    public static int dropColumn(String dbName, String tableName, String columnName)
    {
        System.out.println("删除列："+columnName);
        // DB db = null; // 移除了未使用的DB声明
        try {
            // 准备列定义数组，删除操作只需要列名
            String[] columnDef = new String[1];
            columnDef[0] = columnName;
            
            // DataManager.updateColumnStructure 会处理事务和DB实例
            // 如果它失败，会抛出异常，该异常应由此方法向上传播
            return storage.data.DataManager.updateColumnStructure(dbName, tableName, "DROP", columnDef);
        } catch (Exception e) {
            System.err.println("❌ (TableManager)删除列时发生错误: " + dbName + "." + tableName + "." + columnName + " - " + e.getMessage());
            // 重新抛出异常，以便上层（如Session）可以处理事务回滚
            throw e;
        }
    }
    
    public static int modifyColumn(String dbName, String tableName, String columnName, String dataType)
    {
        System.out.println("将列"+columnName+"的数据类型改为"+dataType);
        // DB db = null; // 移除了未使用的DB声明
        try {
            // 解析数据类型，提取类型和长度
            String type = dataType;
            String lengthStr = "255"; // 默认长度
            
            if (dataType.contains("(") && dataType.contains(")")) {
                int openBracket = dataType.indexOf("(");
                int closeBracket = dataType.indexOf(")");
                
                if (openBracket > 0 && closeBracket > openBracket) {
                    type = dataType.substring(0, openBracket).trim();
                    lengthStr = dataType.substring(openBracket + 1, closeBracket).trim();
                }
            }
            
            String[] columnDef = new String[3];
            columnDef[0] = columnName;
            columnDef[1] = type;
            columnDef[2] = lengthStr;
            
            // DataManager.updateColumnStructure 会处理事务和DB实例
            // 如果它失败，会抛出异常，该异常应由此方法向上传播
            return storage.data.DataManager.updateColumnStructure(dbName, tableName, "MODIFY", columnDef);
        } catch (Exception e) {
            System.err.println("❌ (TableManager)修改列时发生错误: " + dbName + "." + tableName + "." + columnName + " - " + e.getMessage());
            // 重新抛出异常，以便上层（如Session）可以处理事务回滚
            throw e; 
        }
    }

    /**
     * (新) 向表中添加指定的约束。
     * 此方法将依赖 sql.constraints 包中的逻辑进行校验和准备工作。
     *
     * @param dbName 数据库名称
     * @param tableName 表名称
     * @param newConstraint 要添加的 TableConstraint 对象
     * @return 1 表示成功，0 表示失败
     */
    @SuppressWarnings("unchecked")
    public static int addConstraint(String dbName, String tableName, TableConstraint newConstraint) {
        DB db = TransactionLogger.getInstance().getCurrentTransactionDB();
        if (db == null) {
            System.err.println("错误 (addConstraint): 操作需要在活动的事务中执行。");
            return 0;
        }
        String activeTransactionDbName = TransactionLogger.getInstance().getTransactionDatabaseName();
        if (activeTransactionDbName == null || !activeTransactionDbName.equals(dbName)) {
            System.err.println("错误 (addConstraint): 操作的数据库 '" + dbName + "' 与当前活动事务的数据库 '" + activeTransactionDbName + "' 不符。");
            return 0;
        }
        if (db.isClosed()) {
            System.err.println("错误 (addConstraint): 与当前事务关联的数据库实例已关闭。");
            TransactionLogger.getInstance().rollbackTransaction(); // 应该由调用者处理，但这里先记录
            return 0;
        }

        try {
            // 1. 检查表是否存在
            Map<String, Object> tableInfoMap = (Map<String, Object>) db
                .hashMap("TableInfo")
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("tables", new HashMap<String, Object>());
            List<String> tablesList = (List<String>) tableInfoMap.getOrDefault("tablesList", new ArrayList<String>());
            if (!tablesList.contains(tableName)) {
                System.err.println("❌ (addConstraint) 表 [" + tableName + "] 不存在。");
                return 0;
            }

            // 2. 加载现有列元数据和约束元数据
            List<Map<String, Object>> columnsMetaList = (List<Map<String, Object>>) db
                .hashMap("columns_" + tableName)
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("metadata", new ArrayList<Map<String, Object>>());

            List<Map<String, Object>> existingConstraintsMapList = (List<Map<String, Object>>) db
                .hashMap("constraints_" + tableName)
                .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA)
                .createOrOpen().getOrDefault("metadata", new ArrayList<Map<String, Object>>());
            
            List<TableConstraint> existingConstraints = new ArrayList<>();
            for (Map<String, Object> map : existingConstraintsMapList) {
                try {
                    existingConstraints.add(TableConstraint.fromMap(map));
                } catch (IllegalArgumentException e) {
                    System.err.println("⚠️ (addConstraint) 加载现有约束时出错: " + e.getMessage() + " - 跳过此约束。");
                }
            }

            // 调用 ConstraintUtils 进行校验和准备
            PrepareConstraintChangesResult preparationResult = ConstraintUtils.prepareConstraintForAddition(
                newConstraint, 
                existingConstraints, 
                columnsMetaList, // Pass the original (or a fresh copy if prepare... modifies it internally)
                tableName
            );

            if (!preparationResult.isValid) {
                System.err.println("❌ (addConstraint) 添加约束失败: " + preparationResult.errorMessage);
                return 0;
            }

            // 获取需要持久化的变更
            List<TableConstraint> finalConstraintsToAddOrUpdate = preparationResult.constraintsToAdd;
            List<Map<String, Object>> updatedColumnMetaForSave = preparationResult.updatedColumnMetaList;

            // 将 existingConstraintsMapList 更新为包含所有最终要保存的约束
            // (包括旧的未改变的约束，以及 preparationResult.constraintsToAdd 中的新约束或替换的约束)
            // 一个更稳妥的方式是：
            // 1. 从 existingConstraints 开始，移除那些被新约束替代的（例如，如果PK被替换，或者未来实现DROP CONSTRAINT）。
            // 2. 然后添加所有来自 preparationResult.constraintsToAdd 的约束。
            // 当前 prepareConstraintForAddition 设计为返回所有需要添加的新约束，
            // 而 TableManager 需要合并它们到现有的约束列表中 (处理更新/替换的逻辑在prepare...内部完成)
            
            // 简化：直接使用 preparationResult.constraintsToAdd 来构建最终要保存的约束列表。
            // 这假设 prepareConstraintForAddition 如果处理替换，会包含完整的最终列表。
            // 或者，更准确地说，TableManager 应该将现有的和新准备的合并。
            // 让我们假设 prepareConstraintForAddition 内部处理了去重和替换，并返回了 *新增的* 或 *变更后* 的约束。
            // TableManager 则需要将这些变更合并到其持有的 existingConstraintsMapList 中。

            // 正确的合并逻辑：
            List<TableConstraint> allCurrentConstraints = new ArrayList<>(existingConstraints);
            // Remove any constraint that newConstraint is intended to replace (e.g., an old PK if newConstraint is a PK)
            if (newConstraint.getType() == ConstraintType.PRIMARY_KEY) {
                allCurrentConstraints.removeIf(c -> c.getType() == ConstraintType.PRIMARY_KEY);
            }
            // Remove any constraint with the same name as any in constraintsToAdd (as they are replacements or new)
            // This might be too broad if constraintsToAdd contains items not directly related to newConstraint (e.g. auto-NN)
            // Better: if newConstraint has a name that exists, it's an error (caught by validateConstraintName)
            // So, constraintsToAdd are genuinely new or part of a compound operation like new PK + its NN.
            allCurrentConstraints.addAll(preparationResult.constraintsToAdd);
            
            // Remove duplicates by name, keeping the ones from constraintsToAdd (effectively an update)
            List<TableConstraint> distinctFinalConstraints = new ArrayList<>();
            Map<String, TableConstraint> constraintMapByName = new HashMap<>();
            for(TableConstraint c : allCurrentConstraints) constraintMapByName.put(c.getName(), c); // last one wins if names collide, should be new ones
            distinctFinalConstraints.addAll(constraintMapByName.values());
            
            List<Map<String, Object>> finalConstraintsToSaveMapListCorrected = new ArrayList<>();
            for(TableConstraint tc : distinctFinalConstraints) finalConstraintsToSaveMapListCorrected.add(tc.toMap());


            // 3. 持久化更新后的约束列表
            db.hashMap("constraints_" + tableName)
              .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", finalConstraintsToSaveMapListCorrected);

            // 4. 如果列元数据被修改，则持久化
            if (updatedColumnMetaForSave != null) { // updatedColumnMetaForSave is the full list of columns from prepare...
                db.hashMap("columns_" + tableName)
                  .keySerializer(Serializer.STRING).valueSerializer(Serializer.JAVA).createOrOpen().put("metadata", updatedColumnMetaForSave);
                 System.out.println("ℹ️ (addConstraint) 列元数据已更新。");
            }

            System.out.println("✅ (addConstraint) 约束 [" + newConstraint.getName() + "] (和任何相关约束) 已成功准备并存储 (事务中排队)");
            return 1;

        } catch (Exception e) {
            System.err.println("❌ (addConstraint) 添加约束到表 [" + tableName + "] 时失败: " + e.getMessage());
            // e.printStackTrace(); // for debugging
            // 事务回滚应由更高层调用者 (SQLExecutor) 处理
            throw e; // 或者包装成特定的 StorageException/ConstraintException
        }
    }

    public static int dropConstraint(String dbName, String tableName, String constraintNameToDrop) throws StorageException {
        TransactionLogger txLogger = TransactionLogger.getInstance();
        DB db = txLogger.getCurrentTransactionDB();

        if (db == null) {
            throw new StorageException("TableManager.dropConstraint: Operation must be within an active transaction.");
        }
        if (!dbName.equals(txLogger.getTransactionDatabaseName())) {
            throw new StorageException("TableManager.dropConstraint: Database context mismatch with active transaction.");
        }
        if (db.isClosed()) {
            throw new StorageException("TableManager.dropConstraint: Database for active transaction is closed.");
        }

        String constraintsMapName = "constraints_" + tableName;
        String metadataKey = "metadata";

        try {
            // 1. Load current constraints directly using MapDB from the transaction
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> currentConstraintsMapList = (List<Map<String, Object>>) db
                .hashMap(constraintsMapName, Serializer.STRING, Serializer.JAVA)
                .createOrOpen()
                .getOrDefault(metadataKey, new ArrayList<Map<String, Object>>());

            // It's good practice to work on a copy for modifications
            List<TableConstraint> currentConstraints = new ArrayList<>();
            for (Map<String, Object> map : currentConstraintsMapList) {
                try {
                    currentConstraints.add(TableConstraint.fromMap(map));
                } catch (IllegalArgumentException e) {
                    System.err.println("WARN (dropConstraint): Skipping invalid constraint map during load: " + map + " - Error: " + e.getMessage());
                }
            }
            
            // For potential manual logging or if MapDB rollback isn't covering all desired atomicity aspects
            // We might not need this explicit original list if MapDB handles transactional rollback perfectly.
            // List<Map<String, Object>> originalConstraintsMapListForLog = deepCopyMapList(currentConstraintsMapList); 
            // For now, we assume MapDB's transaction handles rollback of .put() operations.

            TableConstraint constraintToRemove = null;
            int foundAtIndex = -1;

            for (int i = 0; i < currentConstraints.size(); i++) {
                if (currentConstraints.get(i).getName().equalsIgnoreCase(constraintNameToDrop)) {
                    constraintToRemove = currentConstraints.get(i);
                    foundAtIndex = i;
                    break;
                }
            }

            if (constraintToRemove == null) {
                throw new StorageException("约束 '" + constraintNameToDrop + "' 在表 '" + tableName + "' 中未找到。");
            }

            currentConstraints.remove(foundAtIndex);
            System.out.println("[DEBUG] Removed main constraint from list: " + constraintToRemove.getName() + " type " + constraintToRemove.getType());

            if (constraintToRemove.getType() == ConstraintType.PRIMARY_KEY) {
                final String pkConstraintName = constraintToRemove.getName(); // Effectively final for lambda
                List<String> pkColumns = constraintToRemove.getColumns();
                currentConstraints.removeIf(tc -> 
                    tc.getType() == ConstraintType.NOT_NULL &&
                    pkColumns.stream().anyMatch(pkColName -> { 
                        String autoNNName = ConstraintUtils.generateAutoNotNullConstraintName(pkColName, pkConstraintName);
                        return tc.getName().equals(autoNNName) && tc.getColumns().contains(pkColName) && tc.getColumns().size() == 1;
                    })
                );
                System.out.println("[DEBUG] Removed auto-generated NOT NULL constraints associated with PK " + pkConstraintName);
            }

            List<Map<String, Object>> updatedConstraintsMapList = currentConstraints.stream()
                .map(TableConstraint::toMap)
                .collect(Collectors.toList());

            // 2. Persist the updated constraints list directly using MapDB within the transaction
            db.hashMap(constraintsMapName, Serializer.STRING, Serializer.JAVA)
              .createOrOpen()
              .put(metadataKey, updatedConstraintsMapList);
            
            // Logging (if any) would be handled by TransactionLogger.commit or implicitly by MapDB's transactional nature.
            // No explicit txLogger.logOperation(...) is called here as it seems not to exist in that form.
            System.out.println("[INFO] TableManager.dropConstraint: Constraint '" + constraintNameToDrop + "' removal prepared in transaction.");
            
            System.out.println("✅ 约束 '" + constraintNameToDrop + "' 已成功计划从表 '" + tableName + "' 中删除。");
            return 1; // Success

        } catch (Exception e) {
            // Log or handle exception, then rethrow if it needs to trigger a transaction rollback by the caller
            System.err.println("❌ Error in TableManager.dropConstraint for " + tableName + "." + constraintNameToDrop + ": " + e.getMessage());
            // e.printStackTrace(); // for detailed debugging
            if (e instanceof StorageException) {
                throw e; // Rethrow known StorageException
            }
            throw new StorageException("删除约束时发生内部错误: " + e.getMessage(), e); // Wrap other exceptions
        }
    }

    // Helper for deep copy if needed, though for now relying on MapDB transactionality
    /*
    private static List<Map<String, Object>> deepCopyMapList(List<Map<String, Object>> originalList) {
        if (originalList == null) return null;
        List<Map<String, Object>> newList = new ArrayList<>();
        for (Map<String, Object> map : originalList) {
            newList.add(new HashMap<>(map)); // Shallow copy of map entries is usually enough if values are immutable or also copied if complex
        }
        return newList;
    }
    */

    /**
     * 将表中的列设置为非空
     *
     * @param dbName 数据库名
     * @param tableName 表名
     * @param columnName 需要设置为非空的列名
     * @return 1: 成功, 0: 失败
     */
    @SuppressWarnings("unchecked")
    public static int setColumnNotNull(String dbName, String tableName, String columnName) {
        // 1. 从TransactionLogger获取DB实例
        DB db = TransactionLogger.getInstance().getCurrentTransactionDB();

        // 2. 检查事务状态和数据库上下文
        if (db == null) {
            System.err.println("错误: setColumnNotNull 操作需要在活动的事务中执行。");
            return 0;
        }
        String activeTransactionDbName = TransactionLogger.getInstance().getTransactionDatabaseName();
        if (activeTransactionDbName == null || !activeTransactionDbName.equals(dbName)) {
            System.err.println("错误: setColumnNotNull 操作的数据库 '" + dbName + "' 与当前活动事务的数据库 '" + activeTransactionDbName + "' 不符。");
            return 0;
        }
        if (db.isClosed()) {
            System.err.println("错误: 与当前事务关联的数据库实例已关闭。事务将被回滚。");
            TransactionLogger.getInstance().rollbackTransaction();
            return 0;
        }

        try {
            // 3. 获取表的列信息
            List<Map<String, Object>> columnsInfo = (List<Map<String, Object>>) db
                .hashMap("columns_" + tableName)
                .keySerializer(Serializer.STRING)
                .valueSerializer(Serializer.JAVA)
                .createOrOpen()
                .getOrDefault("metadata", new ArrayList<Map<String, Object>>());

            // 4. 查找指定的列并修改nullable属性
            boolean columnFound = false;
            for (Map<String, Object> columnInfo : columnsInfo) {
                String colName = (String) columnInfo.get("name");
                if (colName != null && colName.equalsIgnoreCase(columnName)) {
                    columnInfo.put("nullable", false);
                    columnFound = true;
                    break;
                }
            }

            if (!columnFound) {
                System.err.println("❌ 表 [" + tableName + "] 中未找到列 [" + columnName + "]");
                return 0;
            }

            // 5. 保存修改后的列信息
            db.hashMap("columns_" + tableName)
              .keySerializer(Serializer.STRING)
              .valueSerializer(Serializer.JAVA)
              .createOrOpen()
              .put("metadata", columnsInfo);

            System.out.println("✅ 已在事务中将表 [" + tableName + "] 的列 [" + columnName + "] 设置为非空");
            return 1;
        } catch (Exception e) {
            System.err.println("❌ 设置列非空属性时发生错误: " + e.getMessage());
            throw e;
        }
    }

}
